
# benötigte Bibliotheken importieren

# für Daten
import numpy as np
import pandas as pd
 
# für Grafik
import plotly.express as px


# Datensatz einlesen - erstmal mit /n als sep, um das Trennzeichen zu erkennen

df_input = pd.read_csv("data_abschlussprojekt.csv", sep="/n", engine='python', nrows=2)
df_input 

# Trennzeichen ist # und erste Spalte ist index


# Daten einlesen mit dem korrekten Trennzeichen

df_kiva = pd.read_csv("data_abschlussprojekt.csv", sep="#", index_col=0)
df_kiva.head(2)


# Spalten anzeigen

df_kiva.columns

Index([' funded_amount', ' loan_amount', ' activity', ' sector', ' use',
       ' country_code', ' country', ' region', ' currency', ' term_in_months',
       ' lender_count', ' borrower_genders', ' repayment_interval'],
      dtype='object')


# Leerzeichen entfernen in den Spalten

df_kiva.columns = df_kiva.columns.str.strip()


# check, ob Leerzeichen entfernt sind

df_kiva.columns

Index(['funded_amount', 'loan_amount', 'activity', 'sector', 'use',
       'country_code', 'country', 'region', 'currency', 'term_in_months',
       'lender_count', 'borrower_genders', 'repayment_interval'],
      dtype='object')


# Datentypen

df_kiva.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


# kleine statistische Auswertung

df_kiva.describe()


df_kiva.loc[df_kiva.duplicated(),:]


df_kiva_copy = df_kiva.copy()

columns_to_check = ['funded_amount','loan_amount','activity','sector','country_code','term_in_months','lender_count']
duplicate_rows = df_kiva_copy[df_kiva_copy.duplicated(subset=columns_to_check,keep=False)]

use_nan_mask = duplicate_rows['use'].isna()
duplicate_rows = duplicate_rows[use_nan_mask]

columns_to_check.append('use')
final_duplicates = df_kiva_copy[df_kiva_copy.duplicated(subset=columns_to_check,keep=False)]
final_duplicates


# Identifizierung über Datentyp - abgleich datentyp mit Spalteninhalt
                                                                              
df_kiva.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


# prüfen, welcher fehlende Werte in df_kiva sind:                                                                            
df_kiva.isnull().sum()

funded_amount             0
loan_amount               0
activity                  0
sector                    0
use                    4232
country_code              8
country                   0
region                56800
currency                  0
term_in_months            0
lender_count              0
borrower_genders       4221
repayment_interval        0
dtype: int64


df_kiva.isnull().mean() * 100

funded_amount         0.000000
loan_amount           0.000000
activity              0.000000
sector                0.000000
use                   0.630508
country_code          0.001192
country               0.000000
region                8.462392
currency              0.000000
term_in_months        0.000000
lender_count          0.000000
borrower_genders      0.628869
repayment_interval    0.000000
dtype: float64


# Prüfen in df_kiva welche fehlende Werte gibt?

df_kiva[df_kiva.isna().any(axis=1)]


df_kiva.loc[df_kiva.loc[:, 'use'].isna(), :]


df_kiva['use'].unique()

array(['To buy seasonal, fresh fruits to sell. ',
       'to repair and maintain the auto rickshaw used in their business.',
       'To repair their old cycle-van and buy another one to rent out as a source of income',
       ...,
       'Pretend the issue with loan got addressed by Kiva Coordinator.',
       'Kiva Coordinator replaced loan use. Should see this in viewdiff.',
       'Edited loan use in english.'], dtype=object)


# prüfen, welcher fehlende wert in country_code ist
# 8

print(df_kiva.loc[df_kiva.loc[:, 'country_code'].isna(), :].shape)
df_kiva.loc[df_kiva.loc[:, 'country_code'].isna(), :]

(8, 13)


df_kiva['country_code'].unique()

array(['PK', 'IN', 'KE', 'NI', 'SV', 'TZ', 'PH', 'PE', 'SN', 'KH', 'LR',
       'VN', 'IQ', 'HN', 'PS', 'MN', 'US', 'ML', 'CO', 'TJ', 'GT', 'EC',
       'BO', 'YE', 'GH', 'SL', 'HT', 'CL', 'JO', 'UG', 'BI', 'BF', 'TL',
       'ID', 'GE', 'UA', 'XK', 'AL', 'CD', 'CR', 'SO', 'ZW', 'CM', 'TR',
       'AZ', 'DO', 'BR', 'MX', 'KG', 'AM', 'PY', 'LB', 'WS', 'IL', 'RW',
       'ZM', 'NP', 'CG', 'MZ', 'ZA', 'TG', 'BJ', 'BZ', 'SR', 'TH', 'NG',
       'MR', 'VU', 'PA', 'VI', 'VC', 'LA', 'MW', 'MM', 'MD', 'SS', 'SB',
       'CN', 'EG', 'GU', 'AF', 'MG', nan, 'PR', 'LS', 'CI', 'BT'],
      dtype=object)


# Überprüfen of country "Namibia" schon ein country_code definiert?
# Falls nicht, ISO-2 von Namibia und replace to "NA"
df_kiva.loc[df_kiva["country"]=="Namibia", :]


df_kiva['region'].unique().shape # 12696
df_kiva['region'].unique()

array(['Lahore', 'Maynaguri', 'Abdul Hakeem', ..., 'Gbenikoro Village',
       'Morimaraia', 'alejandria'], dtype=object)


# # prüfen, welcher fehlende wert in region ist

df_kiva.loc[df_kiva.loc[:, 'region'].isna(), :] # 56800

df_region = df_kiva.loc[df_kiva.loc[:, 'region'].isna(), ['sector','activity','country','region']]
df_region


# prüfen, welcher fehlende werte in borrower_genders ist

# term_in_months sind nur ganzzahlig --> muss int werden

print(df_kiva.loc[:,'borrower_genders'].unique().shape)  # 11299 differents values

df_kiva.loc[df_kiva.loc[:, 'borrower_genders'].isna(), :]

(11299,)


# prüfen, welcher fehlende wert in use ist
# total 4232 
df_kiva.loc[df_kiva.loc[:, 'use'].isna(),:]


# Replace NaN Werte als Unknown

df_kiva['use'].fillna('Unknown', inplace=True)


# Überprüft das Ergebnisse
#df_kiva.loc[df_kiva.loc[:, 'use'].isna(), :]
df_kiva['use'].isnull().sum()

0


df_kiva.loc[df_kiva.loc[:, 'country_code'].isna(), :] # 8 Zeilen


# df_kiva.loc[df_kiva.loc[:, 'country_code'].isna(), :].replace(to_replace="country_code",value="NA", inplace=True)

df_kiva.loc[df_kiva['country_code'].isna(), 'country_code'] = 'NA'

# df_ecar.replace(to_replace={"Seats":signs}, value=np.nan, inplace=True)


df_kiva.loc[df_kiva.loc[:, 'country_code'].isna(),:]


df_kiva['country_code'].isnull().sum()

0


df_kiva.loc[:, 'country_code'].isna()

0         False
1         False
2         False
3         False
4         False
          ...  
671200    False
671201    False
671202    False
671203    False
671204    False
Name: country_code, Length: 671205, dtype: bool


print(df_kiva["country"].unique().shape) # 87 countries in df_kiva

df_kiva.loc[df_kiva.loc[:, 'region'].isna(),:]

(87,)


# Replace alle NaN Werte mit 'Unknown'
df_kiva['region'].fillna('Unknown', inplace=True)


# Überprüfen noch mal das Ergebnisse
#df_kiva.loc[df_kiva.loc[:, 'region'].isna(),:]
df_kiva['region'].isnull().sum()

0


df_kiva.loc[df_kiva["borrower_genders"].isna(),:]


# Replace alle NaN Werte mit 'Unknown'
df_kiva['borrower_genders'].fillna('Unknown', inplace=True)


# Überprüfen nochmal das Ergebnisse
#df_kiva.loc[df_kiva["borrower_genders"].isna(),:]
df_kiva['borrower_genders'].isnull().sum()

0


df_kiva.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


df_kiva.memory_usage(deep=True)

Index                  5369640
funded_amount          5369640
loan_amount            5369640
activity              46521289
sector                43603755
use                   78992889
country_code          39601095
country               43923974
region                47895732
currency              40272300
term_in_months         5369640
lender_count           5369640
borrower_genders      47174152
repayment_interval    43400106
dtype: int64


# umwandlung von float in den int

df_kiva['term_in_months'] = df_kiva['term_in_months'].astype(int)

df_kiva.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months          int32
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


df_kiva['term_in_months'].unique()

array([ 12,  11,  43,  14,   4,  13,  10,   8,   5,  20,   7,   3,  17,
        15,   9,  39,  23,  26,   6,  30,  22,  25,  27,  16,  52,  18,
        36,  19,  28,  62,  32,  24,  21,  50,  38,   2,  35,  72, 137,
         1,  49,  33,  42,  29,  37,  51, 113,  79,  31,  44,  74,  34,
        48, 114,  40, 124, 104,  63,  85,  78,  70,  86,  61,  60,  67,
        55,  53,  41,  68, 143,  77, 130,  45, 111, 134, 107, 142, 148,
        56, 122, 133, 141, 110,  81, 106,  54, 147, 112,  59, 145, 121,
       109,  80,  47,  97,  75, 101, 128,  98,  87,  71,  66,  46, 125,
        76,  73, 120, 144, 118, 131,  65, 108,  58, 123,  84,  99,  82,
        92,  69,  91,  57,  90,  93, 129,  89,  88,  64, 126, 138, 158,
        83, 100, 105, 132,  96, 127, 135,  95, 154, 156,  94, 115, 102,
       116, 136, 103, 139, 146])


df_kiva.memory_usage(deep=True)

Index                  5369640
funded_amount          5369640
loan_amount            5369640
activity              46521289
sector                43603755
use                   78992889
country_code          39601095
country               43923974
region                47895732
currency              40272300
term_in_months         2684820
lender_count           5369640
borrower_genders      47174152
repayment_interval    43400106
dtype: int64


# benötigte bib

import seaborn as sns


sns.pairplot(data=df_kiva,corner=True)

<seaborn.axisgrid.PairGrid at 0x1ce8c54d4b0>


df_agg = df_kiva.groupby("sector").size()
df_agg

sector
Agriculture       180302
Arts               12060
Clothing           32742
Construction        6268
Education          31013
Entertainment        830
Food              136657
Health              9223
Housing            33731
Manufacturing       6208
Personal Use       36385
Retail            124494
Services           45140
Transportation     15518
Wholesale            634
dtype: int64


df_agg1 = df_kiva.groupby("activity").size()
df_agg1

activity
Adult Care                2
Agriculture           27023
Air Conditioning         36
Animal Sales           9237
Aquaculture             108
                      ...  
Water Distribution      564
Weaving                2961
Wedding Expenses        405
Well digging             42
Wholesale               357
Length: 163, dtype: int64


sns.barplot(x=df_agg, y=df_agg.index)

<Axes: ylabel='sector'>


sns.boxplot(x="term_in_months", data=df_kiva)

<Axes: xlabel='term_in_months'>


# df_kiva.loc[df_kiva["term_in_months"]>100,:]


Anzahl = []
bg_male = []
bg_female = []

for i in df_kiva.loc[:,'borrower_genders']:
    gender_list = str(i).split(', ')
    female_count = gender_list.count('female')
    male_count = gender_list.count('male')
    bg_male.append(male_count)
    bg_female.append(female_count)
    total_num = female_count + male_count
    Anzahl.append(total_num)
    #print("Anzahl:", total_num)

df_kiva_gender = df_kiva.copy()

Anzahl_Genders = "Anzahl_Genders"
bg_male1 = "bg_male"
bg_female1 = "bg_female"
df_kiva_gender[Anzahl_Genders] = Anzahl
df_kiva_gender[bg_female1] = bg_female
df_kiva_gender[bg_male1] = bg_male 
df_kiva_gender


# Zeigen (%)Erfolgsprozentsatz = (Erreichter Betrag / Zielbetrag) * 100

prozent = 0
success_fund = []

for index, row in df_kiva_gender.iterrows():
    funded_amount = row['funded_amount']
    loan_amount = row['loan_amount']
    if loan_amount != 0:
        prozent = (funded_amount / loan_amount) * 100
    success_fund.append(prozent)

df_success_fund = df_kiva_gender.copy()
success_fund1 = "success_fund"
df_success_fund[success_fund1] = success_fund
df_success_fund


df_success_fund["success_fund"].unique()

array([100.        ,  85.5       ,  80.20833333, ...,  52.21238938,
        49.66442953,   8.97435897])


df_final = df_success_fund.copy()

country_mapping = {
    'AF': 'Africa',
    'AL': 'Europe',
    'AM': 'Asia',
    'AZ': 'Asia',
    'BF': 'Africa',
    'BI': 'Africa',
    'BJ': 'Africa',
    'BO': 'South America',
    'BR': 'South America',
    'BT': 'Asia',
    'BZ': 'North America',
    'CD': 'Africa',
    'CG': 'Africa',
    'CI': 'Africa',
    'CL': 'South America',
    'CM': 'Africa',
    'CN': 'Asia',
    'CO': 'South America',
    'CR': 'North America',
    'DO': 'North America',
    'EC': 'South America',
    'EG': 'Africa',
    'GE': 'Asia',
    'GH': 'Africa',
    'GT': 'South America',
    'GU': 'Oceania',
    'HN': 'North America',
    'HT': 'North America',
    'ID': 'Asia',
    'IL': 'Asia',
    'IN': 'Asia',
    'IQ': 'Asia',
    'JO': 'Asia',
    'KE': 'Africa',
    'KG': 'Asia',
    'KH': 'Asia',
    'LA': 'Asia',
    'LB': 'Asia',
    'LR': 'Africa',
    'LS': 'Africa',
    'MD': 'Europe',
    'MG': 'Africa',
    'ML': 'Africa',
    'MM': 'Asia',
    'MN': 'Asia',
    'MR': 'Africa',
    'MW': 'Africa',
    'MX': 'North America',
    'MZ': 'Africa',
    'NA': 'Africa',
    'NG': 'Africa',
    'NI': 'North America',
    'NP': 'Asia',
    'PA': 'North America',
    'PE': 'South America',
    'PH': 'Asia',
    'PK': 'Asia',
    'PR': 'North America',
    'PS': 'Asia',
    'PY': 'South America',
    'RW': 'Africa',
    'SB': 'Oceania',
    'SL': 'Africa',
    'SN': 'Africa',
    'SO': 'Africa',
    'SR': 'South America',
    'SS': 'Africa',
    'SV': 'North America',
    'TG': 'Africa',
    'TH': 'Asia',
    'TJ': 'Asia',
    'TL': 'Asia',
    'TR': 'Asia',
    'TZ': 'Africa' ,
    'UA': 'Europe',
    'UG': 'Africa',
    'US': 'North America',
    'VC': 'North America',
    'VI': 'North America',
    'VN': 'Asia',
    'VU': 'Oceania',
    'WS': 'Oceania',
    'XK': 'Europe',
    'YE': 'Asia' ,
    'ZA': 'Africa',
    'ZM': 'Africa',
    'ZW': 'Africa'
}

df_final.loc[:,"continent"] = df_final.loc[:,"country_code"].map(country_mapping)
df_final.head()


# sind Datentypen passend?

df_final.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months          int32
lender_count            int64
borrower_genders       object
repayment_interval     object
Anzahl_Genders          int64
bg_female               int64
bg_male                 int64
success_fund          float64
continent              object
dtype: object


# über nunique() die anzahl der unique werte herausfinden, um herauszufinden, ob der category datentyp Sinn macht

liste = df_final.select_dtypes("object").columns 

for spalte in liste:
    print(spalte, df_final.loc[:,spalte].nunique())

activity 163
sector 15
use 424913
country_code 87
country 87
region 12696
currency 67
borrower_genders 11299
repayment_interval 4
continent 6


# Umwandlung in Category Datentyp - mit astype() 

df_kiva_opti = df_final.copy()

liste_category = ['activity','sector','use','country_code','country','region','currency','borrower_genders','repayment_interval','continent']

for spalte in liste_category:
    df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')

    
# check, ob Umwandlung erfolgreich

df_kiva_opti.dtypes

C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')
C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\2420383145.py:8: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,spalte] = df_final.loc[:,spalte].astype('category')

funded_amount          float64
loan_amount            float64
activity              category
sector                category
use                   category
country_code          category
country               category
region                category
currency              category
term_in_months           int32
lender_count             int64
borrower_genders      category
repayment_interval    category
Anzahl_Genders           int64
bg_female                int64
bg_male                  int64
success_fund           float64
continent             category
dtype: object


df_kiva_opti.memory_usage(deep=True)

Index                  5369640
funded_amount          5369640
loan_amount            5369640
activity               1357871
sector                  672752
use                   72813831
country_code            678442
country                 679029
region                 2911520
currency                677329
term_in_months         2684820
lender_count           5369640
borrower_genders       3539048
repayment_interval      671633
Anzahl_Genders         5369640
bg_female              5369640
bg_male                5369640
success_fund           5369640
continent               671768
dtype: int64


# Optimierung des Speicherplatzes bei den floats

df_kiva_opti.loc[:,["funded_amount","loan_amount","success_fund"]] = df_kiva_opti[["funded_amount","loan_amount","success_fund"]].apply(pd.to_numeric, downcast="float")
df_kiva_opti.dtypes

C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\3400184989.py:3: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,["funded_amount","loan_amount","success_fund"]] = df_kiva_opti[["funded_amount","loan_amount","success_fund"]].apply(pd.to_numeric, downcast="float")

funded_amount          float32
loan_amount            float32
activity              category
sector                category
use                   category
country_code          category
country               category
region                category
currency              category
term_in_months           int32
lender_count             int64
borrower_genders      category
repayment_interval    category
Anzahl_Genders           int64
bg_female                int64
bg_male                  int64
success_fund           float32
continent             category
dtype: object


# Optimierung des Speicherplatzes bei den ints

df_kiva_opti.loc[:,["term_in_months","lender_count","Anzahl_Genders","bg_male","bg_female"]] = df_kiva_opti[["term_in_months","lender_count","Anzahl_Genders","bg_male","bg_female"]].apply(pd.to_numeric, downcast="integer")
df_kiva_opti.dtypes

C:\Users\alfa\AppData\Local\Temp\ipykernel_3240\164655627.py:3: DeprecationWarning: In a future version, `df.iloc[:, i] = newvals` will attempt to set the values inplace instead of always setting a new array. To retain the old behavior, use either `df[df.columns[i]] = newvals` or, if columns are non-unique, `df.isetitem(i, newvals)`
  df_kiva_opti.loc[:,["term_in_months","lender_count","Anzahl_Genders","bg_male","bg_female"]] = df_kiva_opti[["term_in_months","lender_count","Anzahl_Genders","bg_male","bg_female"]].apply(pd.to_numeric, downcast="integer")

funded_amount          float32
loan_amount            float32
activity              category
sector                category
use                   category
country_code          category
country               category
region                category
currency              category
term_in_months           int16
lender_count             int16
borrower_genders      category
repayment_interval    category
Anzahl_Genders            int8
bg_female                 int8
bg_male                   int8
success_fund           float32
continent             category
dtype: object


# Reduzierung des Speicherplatzes
reduction = (df_final.memory_usage(deep=True).sum()-df_kiva_opti.memory_usage(deep=True).sum())/df_final.memory_usage(deep=True).sum()

print(f"{reduction:0.2f}")

0.80


df_kiva_opti


# unsere df csv zwischenspeichern, um diese für die visualiserung zu nutzen

df_kiva_opti.to_csv('kiva_after_preprocessing.csv',sep=',')


# alternativ: to_pickle Vorteil: Abspeicherung als DataFrame inkl. seiner Eigenschaften zum Speicherplatz
#                       Nachteil: wenn Versionen im Team nicht stimmen, kann der andere den DF nicht als pickle einlesen

df_kiva_opti.to_pickle('kiva_after_preprocessing.pkl')


import pandas as pd 
import plotly.express as px 
import numpy as np


# datensatz laden mit pickle

df_kiva_visual = pd.read_pickle('kiva_after_preprocessing.pkl')      
df_kiva_visual.head(2)


df_geo = df_kiva_visual.groupby(by=["continent","country"]).agg(count=("country","count")).reset_index()
df_1 = df_geo.loc[df_geo["count"]>0,:]
df_1


data = {'Category': ['Total Countries', 'Kiva Countries'],
        'Count': [195, 87]}
df = pd.DataFrame(data)


fig = px.pie(df, values='Count', names='Category', color='Category',title='Country Distribution')


fig.update_layout(legend_traceorder="normal",
                     legend_title_text='Number of Countries',
                     title_x=0.45
                      )

fig.update_traces(hoverinfo='percent + value',
                       textinfo='label + percent',            
                       insidetextorientation='horizontal',    
                       textposition='inside' ,               
                       rotation=0                            
                     )  
fig.show()


import plotly.express as px

plot_1 = px.choropleth(data_frame=df_1,
                        locations="country",
                        locationmode="country names",
                        color="count",
                        hover_name="continent",
                        projection="natural earth",
                        color_continuous_scale="Oranges",
                        title="Die Verteilung des Länder auf Kiva Plattform"
                        )

plot_1.update_layout(title_x=0.45)

plot_1.layout.legend.title = "Nr of projects"     

plot_1.show()


df_country = df_final.groupby(by=["continent","country","sector","activity"],as_index=False).agg(count_project=("activity","count"),total_investment=("funded_amount","sum"))
df_country.sort_values(by=["count_project","total_investment"],ascending=False)


df_country_project = df_country.groupby(by="country").agg(sum_project=("count_project","sum")).reset_index()
df_country_project.sort_values(by="sum_project",ascending=False)


plot_2 = px.bar(data_frame=df_country_project,
                x='country',
                y='sum_project',
                title='The distribution of funded projects',
                labels={'country':'Countries','sum_project':'Number of Projects'},
                #size='total_investment',
                color='sum_project',
                height=800,template="simple_white"
                )
plot_2.update_traces(marker_line_width=1)

# es scheint als ob es eine Grenze bei 50k gibt --> daher lege ich eine vertical line an diese Stelle

plot_2.add_shape(type="line",
                 x0=0,
                 y0=20000,
                 x1=len(df_country_project['country']),
                 y1=20000,
                 line=dict(color="black", width=3, dash="dash"))
'''
plot_2.add_shape(type="line",
                 x0=0,
                 y0=40000,
                 x1=len(df_country_invest['country']),
                 y1=40000,
                 line=dict(color="black", width=3, dash="dash"))
'''
plot_2.update_layout(title_x=0.45)
plot_2.update_xaxes(categoryorder="total ascending")

plot_2.show()


# Top-3-Länder mit den meisten finanzierten Projekten: 
df_country_project.sort_values(by="sum_project",ascending=False,inplace=True)
df_country_project.head(3)


# Top-3-Länder mit relativen Sektor 

df_philippine = df_country.loc[df_country["country"]=="Philippines",["country","sector","activity","total_investment"]]
df_2 = df_philippine.groupby(by="sector").agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
#print(df_2)

df_kenya = df_country.loc[df_country["country"]=="Kenya",["country","sector","activity","total_investment"]]
df_3 = df_kenya.groupby(by="sector").agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
#print(df_3)

df_ElSalvador = df_country.loc[df_country["country"]=="El Salvador",["country","sector","activity","total_investment"]]
df_4 = df_ElSalvador.groupby(by="sector").agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
#print(df_4)


import plotly.graph_objects as go

# Data for the pie charts
sizes_1 = df_2["count_activity"]  # Sizes for pie chart 1
sizes_2 = df_3["count_activity"]  # Sizes for pie chart 2
sizes_3 = df_4["count_activity"]  # Sizes for pie chart 3
labels = ['Agriculture', 'Arts', 'Clothing', 'Construction', 'Education',
       'Entertainment', 'Food', 'Health', 'Housing', 'Manufacturing',
       'Personal Use', 'Retail', 'Services', 'Transportation',
       'Wholesale']  # Labels for all pie charts

# Sort sizes and labels in descending order
sizes_1, labels = zip(*sorted(zip(sizes_1, labels), reverse=True))
sizes_2, _ = zip(*sorted(zip(sizes_2, labels), reverse=True))
sizes_3, _ = zip(*sorted(zip(sizes_3, labels), reverse=True))

# Create subplots with 1 row and 3 columns
fig = go.Figure()

# Add the first pie chart
fig.add_trace(go.Pie(
    labels=labels,
    values=sizes_1,
    name="Philippine",
    hole=0.3,
    domain={"x": [0, 0.25]},
    direction="clockwise",
    rotation=0
))

# Add the second pie chart
fig.add_trace(go.Pie(
    labels=labels,
    values=sizes_2,
    name="Kenya",
    hole=0.3,
    domain={"x": [0.35, 0.60]},
    direction="clockwise",
    rotation=0
))

# Add the third pie chart
fig.add_trace(go.Pie(
    labels=labels,
    values=sizes_3,
    name="El Salvador",
    hole=0.3,
    domain={"x": [0.70, 0.95]},
    direction="clockwise",
    rotation=0
))

# Update layout and show the figure
fig.update_layout(
    title="Top 3 Countries with the most invested projects in 15 sectors",
    showlegend=True,
    title_x=0.45,
    annotations=[
        dict(text="Philippine", x=0.09, y=1.1, showarrow=False),
        dict(text="Kenya", x=0.48, y=1.1, showarrow=False),
        dict(text="El Salvador", x=0.90, y=1.1, showarrow=False)
    ]
)

fig.show()


# Um den signifikanten Sektor in Bezug auf die Aktivitäten in den Ländern zu bestimmen

df_2a = df_philippine.groupby(by=["sector","activity"]).agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
df_2a.sort_values(by=["sum_investment"],ascending=False)


df_3a = df_kenya.groupby(by=["sector","activity"]).agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
df_3a.sort_values(by=["sum_investment"],ascending=False)


df_4a = df_ElSalvador.groupby(by=["sector","activity"]).agg(count_activity=("activity","count"),sum_investment=("total_investment","sum")).reset_index()
df_4a.sort_values(by="sum_investment",ascending=False)


# Die Beziehung zwischen Sektors und Aktivitäte in Philippines
fig_philippine = px.scatter(df_2a, x='sector', y='activity', color='count_activity',
                 size='sum_investment', hover_data=['count_activity', 'sum_investment'],
                 title='Relationship between Sectors and Activities',
                 labels={'sector': 'Sector', 'activity': 'Activity'}
                 )

fig_philippine.update_layout(
    xaxis=dict(title='Sectors', categoryorder='total descending'),
    yaxis=dict(title='Activities'),
    title='Phillipines', title_x=0.45,
    legend=dict(title='Nr of activities')
)

fig_philippine.show()


# Die Beziehung zwischen Sektors und Aktivitäte in Kenya
fig_kenya = px.scatter(df_3a, x='sector', y='activity', color='count_activity',
                 size='sum_investment', hover_data=['count_activity', 'sum_investment'],
                 title='Relationship between Sectors and Activities',
                 labels={'sector': 'Sector', 'activity': 'Activity'}
                 )

fig_kenya.update_layout(
    xaxis=dict(title='Sectors', categoryorder='total descending'),
    yaxis=dict(title='Activities'),
    title='Kenya', title_x=0.45,
    legend=dict(title='Nr of activities')
)


fig_kenya.show()


# Die Beziehung zwischen Sektors und Aktivitäte in El Salvardor

fig_elsavaldor = px.scatter(df_4a, x='sector', y='activity', color='count_activity',
                 size='sum_investment', hover_data=['count_activity', 'sum_investment'],
                 title='Relationship between Sectors and Activities',
                 labels={'sector': 'Sector', 'activity': 'Activity'}
                 )

fig_elsavaldor.update_layout(
    xaxis=dict(title='Sectors', categoryorder='total descending'),
    yaxis=dict(title='Activities'),
    title='EL Salvardor', title_x=0.45,
    legend=dict(title='Nr of activities')
)


fig_elsavaldor.show()


# Mapping 5 Kategories für alle Länder
country_categories = {
    'Afghanistan': 'Wirtschaftliches Potenzial',
    'Albania': 'Wachstumschancen',
    'Armenia': 'Rohstoffe',
    'Azerbaijan': 'Infrastrukturprojekte',
    'Belize': 'Technologische Entwicklung',
    'Benin': 'Wachstumschancen',
    'Bhutan': 'Wirtschaftliches Potenzial',
    'Bolivia': 'Rohstoffe',
    'Brazil': 'Wirtschaftliches Potenzial',
    'Burkina Faso': 'Wachstumschancen',
    'Burundi': 'Wachstumschancen',
    'Cambodia': 'Technologische Entwicklung',
    'Cameroon': 'Rohstoffe',
    'Chile': 'Wirtschaftliches Potenzial',
    'China': 'Wirtschaftliches Potenzial',
    'Colombia': 'Rohstoffe',
    'Congo': 'Rohstoffe',
    'Costa Rica': 'Infrastrukturprojekte',
    "Cote D'Ivoire": 'Wachstumschancen',
    'Dominican Republic': 'Infrastrukturprojekte',
    'Ecuador': 'Rohstoffe',
    'Egypt': 'Wirtschaftliches Potenzial',
    'El Salvador': 'Wirtschaftliches Potenzial',
    'Georgia': 'Wachstumschancen',
    'Ghana': 'Rohstoffe',
    'Guam': 'Technologische Entwicklung',
    'Guatemala': 'Infrastrukturprojekte',
    'Haiti': 'Wirtschaftliches Potenzial',
    'Honduras': 'Wachstumschancen',
    'India': 'Wirtschaftliches Potenzial',
    'Indonesia': 'Wachstumschancen',
    'Iraq': 'Rohstoffe',
    'Israel': 'Technologische Entwicklung',
    'Jordan': 'Wachstumschancen',
    'Kenya': 'Wachstumschancen',
    'Kosovo': 'Wirtschaftliches Potenzial',
    'Kyrgyzstan': 'Wachstumschancen',
    "Lao People's Democratic Republic": 'Technologische Entwicklung',
    'Lebanon': 'Infrastrukturprojekte',
    'Lesotho': 'Wachstumschancen',
    'Liberia': 'Wachstumschancen',
    'Madagascar': 'Rohstoffe',
    'Malawi': 'Wachstumschancen',
    'Mali': 'Wirtschaftliches Potenzial',
    'Mauritania': 'Wachstumschancen',
    'Mexico': 'Wirtschaftliches Potenzial',
    'Moldova': 'Wachstumschancen',
    'Mongolia': 'Rohstoffe',
    'Mozambique': 'Rohstoffe',
    'Myanmar (Burma)': 'Wachstumschancen',
    'Namibia': 'Wirtschaftliches Potenzial',
    'Nepal': 'Wachstumschancen',
    'Nicaragua': 'Wachstumschancen',
    'Nigeria': 'Rohstoffe',
    'Pakistan': 'Wirtschaftliches Potenzial',
    'Palestine': 'Infrastrukturprojekte',
    'Panama': 'Infrastrukturprojekte',
    'Paraguay': 'Rohstoffe',
    'Peru': 'Rohstoffe',
    'Philippines': 'Technologische Entwicklung',
    'Puerto Rico': 'Infrastrukturprojekte',
    'Rwanda': 'Wachstumschancen',
    'Saint Vincent and the Grenadines': 'Technologische Entwicklung',
    'Samoa': 'Technologische Entwicklung',
    'Senegal': 'Wachstumschancen',
    'Sierra Leone': 'Wachstumschancen',
    'Solomon Islands': 'Technologische Entwicklung',
    'Somalia': 'Rohstoffe',
    'South Africa': 'Rohstoffe',
    'South Sudan': 'Wachstumschancen',
    'Suriname': 'Rohstoffe',
    'Tajikistan': 'Wachstumschancen',
    'Tanzania': 'Wachstumschancen',
    'Thailand': 'Technologische Entwicklung',
    'The Democratic Republic of the Congo': 'Rohstoffe',
    'Timor-Leste': 'Wachstumschancen',
    'Togo': 'Wachstumschancen',
    'Turkey': 'Wirtschaftliches Potenzial',
    'Uganda': 'Wachstumschancen',
    'Ukraine': 'Wachstumschancen',
    'United States': 'Wirtschaftliches Potenzial',
    'Vanuatu': 'Wachstumschancen',
    'Vietnam': 'Wachstumschancen',
    'Virgin Islands': 'Technologische Entwicklung',
    'Yemen': 'Wachstumschancen',
    'Zambia': 'Rohstoffe',
    'Zimbabwe': 'Rohstoffe'
}
df_country_cat = df_kiva_visual.copy()

df_country_cat['Category'] = df_country_cat['country'].map(country_categories)


df_country_cat


df_categories = df_country_cat.groupby(by=["Category","country","continent"]).agg(nr_project=("country","count")).reset_index()
#df_categories


df_categories.sort_values(by="nr_project",ascending=False)
df_6 = df_categories.loc[df_categories["nr_project"]>0,:]
df_6


# Anzeigen die Anzahl Länder des Kiva-Plattform für jede Gruppe

df_infra = df_country_cat.loc[df_country_cat["Category"]=="Infrastrukturprojekte",["country"]]
print(f"Infrastruktur:",df_infra["country"].unique().shape)
####
df_rohstoff = df_country_cat.loc[df_country_cat["Category"]=="Rohstoffe",["country"]]
print(f"Rohstoff:",df_rohstoff["country"].unique().shape)
###
df_tech = df_country_cat.loc[df_country_cat["Category"]=="Technologische Entwicklung",["country"]]
print(f"Technlogy:",df_tech["country"].unique().shape)
###
df_wachstum = df_country_cat.loc[df_country_cat["Category"]=="Wachstumschancen",["country"]]
print(f"Wachstum:",df_wachstum["country"].unique().shape)
###
df_wirtschaft = df_country_cat.loc[df_country_cat["Category"]=="Wirtschaftliches Potenzial",["country"]]
print(f"Wirtschaft:",df_wirtschaft["country"].unique().shape)

Infrastruktur: (8,)
Rohstoff: (20,)
Technlogy: (11,)
Wachstum: (32,)
Wirtschaft: (16,)


plot_3 = px.scatter(data_frame=df_6,
                x='country',
                y='nr_project',
                title='Analysis of 87 Countries across 5 Categories',
                labels={'Category':'Country Categories','nr_project':'Number of invested projects'},
                color='Category'
               )

plot_3.update_layout(legend=dict(title="Country Categories"),title_x=0.45)
plot_3.show()


plot_3a = px.choropleth(data_frame=df_6,
                        locations="country",
                        locationmode="country names",
                        color="Category",
                        hover_name="continent",
                        projection="natural earth",
                        color_continuous_scale="Oranges",
                        title="Analysis of 87 Countries across 5 Categories"
                        )

plot_3a.update_layout(title_x=0.45)
plot_3a.layout.legend.title = "Country Categories"                  
plot_3a.show()


# für dash-app
from jupyter_dash import JupyterDash
from dash import dcc
from dash import html
from dash.dependencies import Input, Output

# für die Visualisierung
import plotly.express as px 

# Zusatz
from dash import no_update


#df_final


# 1. app erstellen

my_us_app = JupyterDash(__name__)


# 2. layout erstellen

my_us_app.layout = html.Div([
    
                            html.H1("Analysis of 87 Countries across 5 Categories",
                                       style={"font-size": 30, "textAlign":"center"}
                                       ),
    
                            # Radio Items or 5 Country Categories
    
                            dcc.RadioItems(id="radio_select_categories",
                                            options=sorted(df_6.loc[:,"Category"].unique()),
                                            #style={"textAlign": "centergogor"},
                                            style={"textAlign": "center"},
                                            inline=True,
                                            value="Wachstumschancen"
                            
                                        ),
    
                            html.Label(children="Countries",
                                       style={"font-size":20, "text-align":"center"}
                                      ),
    
                            dcc.Dropdown(
                                         id="dd_country",
                                         options=[],
                                         #options=[{"label":s, "value":s} for s in sorted(df_6.loc[:,"country"].unique())],
                                         #value="",
                                         clearable=True,
                                         multi=True,
                                         style={"width":1500}
                                        ),
    
                            
    
                            # Dropdown für die Auswahl des Counties
                            
                            # Graph
    
                            dcc.Graph(
                                      id="my_scatter",
                                      figure={}
                                      )    
    
]) 

# 3. callbacks erstellen

# callback 1 erstellen: Auswahl des States

@my_us_app.callback(
                    Output(component_id="dd_country", component_property="options"),
                    Input(component_id="radio_select_categories", component_property="value")
                    )
def set_country(chosen_category):
    
    # Daten selektieren
    
    df_country = df_6.loc[df_6["Category"]==chosen_category]
    
    return [{"label":s, "value":s} for s in sorted(df_country.loc[:,"country"].unique())]
    

# callback 2 erstellen: zur Verfügung stehende countries sollen als Vorauswahl aufgefüllt werden


@my_us_app.callback(
                    Output(component_id="dd_country", component_property="value"),
                    Input(component_id="dd_country", component_property="options")
                    )

def set_country(available_countries):
    
    return [country["value"] for country in available_countries]


# callback 3 erstellen: Grafik generieren

@my_us_app.callback(
                    Output(component_id="my_scatter", component_property="figure"),
                    Input(component_id="radio_select_categories", component_property="value"),
                    Input(component_id="dd_country", component_property="value") 
                    )
def update_scatter(chose_categories, chosen_country):
    
    if len(chosen_country)==0:
        return no_update
        
    else:
        
        # Daten selektieren
        df = df_6.loc[(df_6["Category"]==chose_categories) & (df_6["country"].isin(chosen_country)),["Category","country","continent"]]
        # Grafik erstellen
    
        my_scatter = px.choropleth(data_frame=df,
                        locations="country",
                        locationmode="country names",
                        color="Category",
                        hover_name="continent",
                        projection="natural earth",
                        color_continuous_scale="Oranges",
                        title="Analysis of 87 Countries across 5 Categories"
                        )
        
        #my_scatter.update_traces(marker=dict(size=12))
        my_scatter.update_layout(title_x=0.45)
        my_scatter.layout.legend.title = "Country Categories"                                         

        return my_scatter

# 4. app ausführen

if __name__ == '__main__':
    my_us_app.run_server(mode='inline', port=8091)

Dash is running on http://127.0.0.1:8091/

	funded_amount	loan_amount	term_in_months	lender_count
count	671205.000000	671205.000000	671205.000000	671205.000000
mean	785.995061	842.397107	13.739022	20.590922
std	1130.398941	1198.660073	8.598919	28.459551
min	0.000000	25.000000	1.000000	0.000000
25%	250.000000	275.000000	8.000000	7.000000
50%	450.000000	500.000000	13.000000	13.000000
75%	900.000000	1000.000000	14.000000	24.000000
max	100000.000000	100000.000000	158.000000	2986.000000

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
202537	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	6.0	162	female	bullet
202823	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	6.0	159	male	bullet
344929	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	120	female	bullet
351177	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	7.0	126	male	bullet
420953	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	118	female	bullet
421218	4000.0	4000.0	Wholesale	Wholesale	purchase solar lighting products for sale to l...	NaN	Namibia	Rundu	NAD	7.0	150	male	bullet
487207	5100.0	5100.0	Renewable Energy Products	Retail	to pay for stock of solar lights and cell phon...	NaN	Namibia	Katima Mulilo	NAD	7.0	183	male	bullet
487653	5000.0	5000.0	Wholesale	Wholesale	to maintain a stock of solar lights and cell p...	NaN	Namibia	Oshakati	NAD	7.0	183	female	bullet

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
202537	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	6.0	162	female	bullet
202823	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	6.0	159	male	bullet
344929	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	120	female	bullet
351177	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	7.0	126	male	bullet
420953	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	118	female	bullet
421218	4000.0	4000.0	Wholesale	Wholesale	purchase solar lighting products for sale to l...	NaN	Namibia	Rundu	NAD	7.0	150	male	bullet
487207	5100.0	5100.0	Renewable Energy Products	Retail	to pay for stock of solar lights and cell phon...	NaN	Namibia	Katima Mulilo	NAD	7.0	183	male	bullet
487653	5000.0	5000.0	Wholesale	Wholesale	to maintain a stock of solar lights and cell p...	NaN	Namibia	Oshakati	NAD	7.0	183	female	bullet

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
202537	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	6.0	162	female	bullet
202823	4150.0	4150.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	6.0	159	male	bullet
344929	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	120	female	bullet
351177	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	Rundu	NAD	7.0	126	male	bullet
420953	3325.0	3325.0	Wholesale	Wholesale	To purchase lighting products for sale to loca...	NaN	Namibia	EEnhana	NAD	7.0	118	female	bullet
421218	4000.0	4000.0	Wholesale	Wholesale	purchase solar lighting products for sale to l...	NaN	Namibia	Rundu	NAD	7.0	150	male	bullet
487207	5100.0	5100.0	Renewable Energy Products	Retail	to pay for stock of solar lights and cell phon...	NaN	Namibia	Katima Mulilo	NAD	7.0	183	male	bullet
487653	5000.0	5000.0	Wholesale	Wholesale	to maintain a stock of solar lights and cell p...	NaN	Namibia	Oshakati	NAD	7.0	183	female	bullet

	# funded_amount# loan_amount# activity# sector# use# country_code# country# region# currency# term_in_months# lender_count# borrower_genders# repayment_interval
0	0#300.0#300.0#Fruits & Vegetables#Food#To buy ...
1	1#575.0#575.0#Rickshaw#Transportation#to repai...

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
0	300.0	300.0	Fruits & Vegetables	Food	To buy seasonal, fresh fruits to sell.	PK	Pakistan	Lahore	PKR	12.0	12	female	irregular
1	575.0	575.0	Rickshaw	Transportation	to repair and maintain the auto rickshaw used ...	PK	Pakistan	Lahore	PKR	11.0	14	female, female	irregular

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
498	100.0	100.0	Home Energy	Personal Use	to buy a solar-powered lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
606	100.0	100.0	Home Energy	Personal Use	to buy a solar-powered lamp.	SV	El Salvador	NaN	USD	14.0	4	male	monthly
808	450.0	450.0	Higher education costs	Education	to pay for one semester's registration fees.	CO	Colombia	Bogotà	COP	7.0	15	female	monthly
1703	500.0	500.0	Higher education costs	Education	To buy a laptop for educational purposes.	SO	Somalia	Hargeisa	USD	8.0	19	male	monthly
2317	250.0	250.0	Poultry	Agriculture	to purchase poultry.	KE	Kenya	Ndaragwa	KES	16.0	10	female	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
51	175.0	175.0	Pigs	Agriculture	to buy piglets and feed	PH	Philippines	Liloy-Dela Paz	PHP	8.0	6	female	irregular
52	175.0	175.0	Pigs	Agriculture	to purchase feed and vitamins for her pigs	PH	Philippines	Tanjay, Negros Oriental	PHP	8.0	7	female	irregular
62	4275.0	4275.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	144	female, female, female, female, female, female...	bullet
191	225.0	225.0	Fish Selling	Food	to buy more fish to sell	PH	Philippines	Minglanilla, Cebu	PHP	11.0	9	female	irregular
237	4275.0	4275.0	Personal Housing Expenses	Housing	to pay for the construction of a hygienic toil...	VN	Vietnam	Hai Duong	VND	14.0	144	female, female, female, female, female, male, ...	bullet
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
5	250.0	250.0	Services	Services	purchase leather for my business using ksh 20000.	KE	Kenya	NaN	KES	4.0	6	female	irregular
49	450.0	450.0	General Store	Retail	to stock his store.	SV	El Salvador	NaN	USD	14.0	18	male	monthly
54	225.0	225.0	Food Market	Food	to purchase various seasonal items to resell: ...	SN	Senegal	NaN	XOF	14.0	7	female	monthly
67	125.0	125.0	Energy	Services	purchase solar lanterns for resale.	KE	Kenya	NaN	KES	3.0	6	male	irregular
70	2000.0	2000.0	Retail	Retail	to install a display window and a sunshade for...	IQ	Iraq	NaN	USD	15.0	71	male	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671197	0.0	25.0	Livestock	Agriculture	Pretend the issue with loan got addressed by K...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
140	2975.0	2975.0	Food Production/Sales	Food	NaN	TZ	Tanzania	NaN	TZS	10.0	110	NaN	monthly
145	1200.0	1200.0	Personal Expenses	Personal Use	NaN	PE	Peru	NaN	PEN	20.0	44	NaN	monthly
170	4250.0	4250.0	Catering	Food	NaN	TZ	Tanzania	NaN	TZS	10.0	116	NaN	monthly
412	2350.0	2350.0	Beauty Salon	Services	NaN	TZ	Tanzania	NaN	TZS	10.0	75	NaN	monthly
414	725.0	725.0	Agriculture	Agriculture	NaN	SV	El Salvador	NaN	USD	20.0	19	NaN	monthly
...	...	...	...	...	...	...	...	...	...	...	...	...	...
671151	0.0	25.0	Livestock	Agriculture	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671174	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671178	0.0	25.0	Livestock	Agriculture	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671185	0.0	25.0	Livestock	Agriculture	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly

	continent	country	count
0	Africa	Afghanistan	2
5	Africa	Benin	497
9	Africa	Burkina Faso	2460
10	Africa	Burundi	880
12	Africa	Cameroon	2230
...	...	...	...
455	South America	Ecuador	13521
461	South America	Guatemala	7310
492	South America	Paraguay	11903
493	South America	Peru	22233
505	South America	Suriname	223

	continent	country	sector	activity	count_project	total_investment
3619	Asia	Philippines	Retail	General Store	42960	15025550.0
506	Africa	Kenya	Agriculture	Farming	20555	10745600.0
3541	Asia	Philippines	Agriculture	Pigs	19985	6039975.0
2290	Asia	Cambodia	Personal Use	Home Appliances	16362	2731325.0
3537	Asia	Philippines	Agriculture	Farming	11924	4063150.0
...	...	...	...	...	...	...
2866	Asia	Jordan	Services	Cleaning Services	1	0.0
4952	North America	Mexico	Retail	Mobile Phones	1	0.0
5187	North America	Puerto Rico	Services	Energy	1	0.0
5297	North America	Virgin Islands	Retail	General Store	1	0.0
5298	North America	Virgin Islands	Services	Services	1	0.0

	country	sum_project
59	Philippines	160441
34	Kenya	75825
22	El Salvador	39875
11	Cambodia	34836
54	Pakistan	26857
...	...	...
83	Virgin Islands	2
0	Afghanistan	2
18	Cote D'Ivoire	1
25	Guam	1
44	Mauritania	1

	sector	activity	count_activity	sum_investment
62	Housing	Personal Housing Expenses	1	2939750.0
0	Agriculture	Agriculture	1	2610050.0
49	Food	Food Production/Sales	1	2463925.0
86	Retail	General Store	1	1914125.0
7	Agriculture	Farming	1	1510900.0
...	...	...	...	...
54	Food	Pub	1	875.0
17	Arts	Musical Instruments	1	800.0
70	Personal Use	Funerals	1	500.0
136	Wholesale	Wholesale	1	500.0
105	Retail	Sporting Good Sales	1	300.0

	Category	country	continent	nr_project
19	Infrastrukturprojekte	Azerbaijan	Asia	1945
105	Infrastrukturprojekte	Costa Rica	North America	1561
117	Infrastrukturprojekte	Dominican Republic	North America	496
161	Infrastrukturprojekte	Guatemala	South America	7310
229	Infrastrukturprojekte	Lebanon	Asia	8792
...	...	...	...	...
2361	Wirtschaftliches Potenzial	Mexico	North America	5741
2388	Wirtschaftliches Potenzial	Namibia	Africa	8
2413	Wirtschaftliches Potenzial	Pakistan	Asia	26857
2551	Wirtschaftliches Potenzial	Turkey	Asia	1703
2571	Wirtschaftliches Potenzial	United States	North America	6093

Abschlussprojekt - Data Analytics¶

Situation¶

CRISP-DM: Business Understanding¶

CRISP-DM: Data Understanding¶

CRISP-DM: Data Preparation¶

Datensatz einlesen¶

Daten kennenlernen¶

Duplikate¶

Definition eines Duplikats¶

Identifizierung von Duplikaten¶

Umgang mit Duplikaten¶

fehlende Werte¶

Schritt 1: fehlende Werte identifizieren¶

Synonyme finden¶

Konvention¶

Schritt 2: Was machen wir mit den fehlenden Werten?¶

Use¶

Country_code¶

Region¶

Borrow_genders¶

Speicherplatz¶

Pairplot - erste visuelle Inspektion¶

Ausreißer / Extremwerte¶

Extrahieren von Informationen aus einer Spalte¶

Rohdaten kombinieren zu KPI´s / Erfolgsfaktor¶

Faktor 1: Erfolgsprozentsatz = (Erreichter Betrag / Zielbetrag) * 100¶

Faktor 2: Kontinent¶

Finale Anpassung des Speicherplatzes¶

check, ob Datentypen passen¶

check, ob Speicherplatz optimal ist¶

Speicherung des DataFrames für die Visualisierung¶

EDA¶

Visualisierung¶

Thema 1: Länder¶

Schritt 1: Business Frage - Wie sieht die globale Präsenz und Akzeptanz der kiva Plattform an?¶

Schritt 2: Plot auswahl¶

Schritt 3: Datenaufbereitung¶

Schritt 4: Customizing des Plot --> Herausarbeiten der Muster¶

Schritt 5: Analyse¶

Deskriptive Part¶

Analytische Part¶

Thema 2: Die Verteilung der finanzierten Projekte¶

Schritt 1: Business Frage - Wie ist die Verteilung der finanzierten Projekte auf die Länder der Kiva-Plattform?¶

Schritt 2: Plot auswahl¶

Schritt 3: Datenaufbereitung¶

Schritt 4: Customizing des Plot --> Herausarbeiten der Muster¶

Schritt 5: Analyse¶

Deskriptive Part¶

Analytische Part¶

Thema 3: Kategorie des Länder¶

Schritt 1: Business Frage - Wie kategorisieren wir die Länder und wie viele Kategorien gibt es?¶

Schritt 2: Plot auswahl¶

Schritt 3: Datenaufbereitung¶

Schritt 4: Customizing des Plot --> Herausarbeiten der Muster¶

Schritt 5: Analyse¶

Deskriptive Part¶

Analytische Part¶

Dashboard¶

Layout¶

Callback¶

Chained Callback¶